package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBank;
import org.compiere.model.MTRMDeposit;
import org.compiere.model.MTRMReward;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;

import extend.org.compiere.utils.Util;

/**
 * @author Aslanbek
 *
 */
public class TRM_RewardReportNew extends SvrProcess {
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	/**Current Record_ID */
	int p_TRM_Deposit_ID = 0;
	/** Period Parameter */
	private Timestamp p_DateAcct_From = null;
	private Timestamp p_DateAcct_To = null;
	/**Out file */
	String ResultPath;
	
	protected void prepare() 
	{	
		log.info("TRM_RewardReportNew Report Prepare method begin");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		p_TRM_Deposit_ID = pi.getRecord_ID();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("TRM_Deposit_ID")) 
				p_TRM_Deposit_ID  = para[i].getParameterAsInt();
			else if (name.equals("DateAcct")) {
				p_DateAcct_From = (Timestamp) para[i].getParameter();
				p_DateAcct_To = (Timestamp) para[i].getParameter_To();
			}
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}

	protected String doIt() throws Exception 
	{
		//File path
		String fileName = "TRM_Reward";
		ResultPath = Util.getResultPath(fileName, fileName.length()+1);
		// 
		  MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		XSSFWorkbook wb = new XSSFWorkbook(entry.getInputStream());
		XSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		MTRMDeposit deposit = new MTRMDeposit(m_ctx, p_TRM_Deposit_ID, get_TrxName());
		MBank bank = new MBank(m_ctx, deposit.getC_Bank_ID(), get_TrxName());
		//Report Header
		sheet.getRow(2).getCell(0).setCellValue(bank.getName()==null? "Банк не указан": bank.getName());
		//Cells style
		R = sheet.getRow(0);
		CellStyle csInt = R.getCell(0).getCellStyle();
		CellStyle csDate = R.getCell(1).getCellStyle();
		CellStyle csNumber = R.getCell(2).getCellStyle();
		CellStyle csText = R.getCell(4).getCellStyle();
		//Report Body
		int rowNo = 4;
		int i=0;
		boolean isFebEnd = false;
//		BigDecimal tempSum = new BigDecimal(0.0);
		String tempFormula = "";
		//days count in year of bank condition
		String daysCount = "%/365*";
		if(deposit.getBankCondition().equals("IN360"))
			daysCount = "%/360*";
		//
		for(MTRMReward reward : deposit.getTRM_Rewards(true, p_DateAcct_From, p_DateAcct_To))
		{
			R = sheet.createRow(rowNo);
			
			//col 1 Индекс
			C = R.createCell(0);
			C.setCellValue(reward.getIndex().intValue());
			C.setCellStyle(csInt);
			
			//col 2 Дата
			//validation end of february
			if(new DateTime(reward.getDateReward()).getMonthOfYear()==2 && new DateTime(reward.getDateReward()).getDayOfMonth()==28)
			{
				isFebEnd = true;
				i++;
			}else
				isFebEnd = false;
			//
			C = R.createCell(1);
			C.setCellValue(isFebEnd?(27+i)+".02."+(new SimpleDateFormat("yyyy").format(reward.getDateReward())):
												   new SimpleDateFormat("dd.MM.yyyy").format(reward.getDateReward()));
			C.setCellStyle(csDate);
				
			//что по факту происходило с этим депозитом
			
			if(!(R.getCell(1).getStringCellValue().substring(0, 6).equals("29.02.")|| 
					R.getCell(1).getStringCellValue().substring(0, 6).equals("30.02.")) )
			{
//				for(MTRMDepositLine depositLine : deposit.getDepositLines(true, reward.getDateReward()))
//					tempSum = tempSum.add(depositLine.getLineSum());
			}
			
			//col 3 Изъятие
			C = R.createCell(2);
			C.setCellValue(0);
//			if(tempSum.doubleValue()<0)
//					C.setCellValue(tempSum.negate().doubleValue());
			if(reward.getwithdrawal().doubleValue()<0)
				C.setCellValue(reward.getwithdrawal().negate().doubleValue());
			C.setCellStyle(csNumber);
			
			//col 4 Пополнение
			C = R.createCell(3);
			C.setCellValue(0);
//			if(tempSum.doubleValue()>0)
//				C.setCellValue(tempSum.doubleValue());
			if(reward.getwithdrawal().doubleValue()>0)
				C.setCellValue(reward.getwithdrawal().doubleValue());
			C.setCellStyle(csNumber);
			
			//col 5 Входящий остаток, по которому было произведено начисление по вкладу
			C = R.createCell(4);
			C.setCellValue(reward.getIncomingBalance().doubleValue());
			C.setCellStyle(csNumber);
			
			//col 6 Фактически начисленное вознаграждение
			C = R.createCell(5);
			
			C.setCellFormula("E"+(rowNo+1)+"*"+reward.getInterestRate()+daysCount+reward.getIndex().intValue());
			C.setCellStyle(csNumber);
						
			//col 7 Фактический поступившая сумма
			C = R.createCell(6);
			C.setCellValue("");
			C.setCellStyle(csNumber);
			
			//Проверка на конец месяца
			DateTime dt = new DateTime(reward.getDateReward());
			boolean isEndOfMonth = false;
			
			if(R.getCell(1).getStringCellValue().substring(0, 6).equals("30.02."))
				isEndOfMonth = true;
			
			if(deposit.getBankCondition().equals("IN360"))
			{
				if((dt.getDayOfMonth()==30))
					isEndOfMonth = true;
					
			}
			else if(deposit.getBankCondition().equals("CalDay"))
			{
				if(dt.equals(dt.dayOfMonth().withMaximumValue()))
					isEndOfMonth = true;
			}
			else
			{
				
			}
			tempFormula = tempFormula+((reward.getwithdrawal().intValue()!=0 && !isEndOfMonth)? "+F"+(rowNo+1):"");
			
			if(isEndOfMonth)//if(dt.equals(dt.dayOfMonth().withMaximumValue()))
			{
				rowNo++;
				
				R = sheet.createRow(rowNo);
				
				//col 1
				C = R.createCell(0);
				C.setCellStyle(csInt);
				//col 2
				C = R.createCell(1);
				C.setCellValue((isFebEnd && deposit.getBankCondition().equals("IN360"))? "На 30.02."+new SimpleDateFormat("yyyy").format(reward.getDateReward()):"На "+new SimpleDateFormat("dd.MM.yyyy").format(reward.getDateReward()));
				C.setCellStyle(csText);
				//col 3
				C = R.createCell(2);
				C.setCellStyle(csNumber);
				//col 4
				C = R.createCell(3);
				C.setCellStyle(csNumber);
				//col 5
				C = R.createCell(4);
				C.setCellStyle(csNumber);
				sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 1, 4));
				//col 6
				C = R.createCell(5);
//				C.setCellFormula(sheet.getRow(rowNo-1).getCell(5).getCellFormula()+tempFormula);
				C.setCellFormula(("F"+rowNo)+tempFormula);
				C.setCellStyle(csNumber);
				//col 7
				C = R.createCell(6);
				C.setCellFormula("F"+(rowNo+1));
				C.setCellStyle(csNumber);
				
				rowNo++;
				
				R = sheet.createRow(rowNo);
				
				//col 1
				C = R.createCell(0);
				C.setCellStyle(csInt);
				
				//col 2
				C = R.createCell(1);
				C.setCellValue("За вычетом налога");
				C.setCellStyle(csText);
				
				//col 3
				C = R.createCell(2);
				C.setCellStyle(csNumber);
				
				//col 4
				C = R.createCell(3);
				C.setCellStyle(csNumber);
				
				//col 5
				C = R.createCell(4);
				C.setCellStyle(csNumber);
				sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 1, 4));
				//col 6
				C = R.createCell(5);
//				C.setCellFormula((sheet.getRow(rowNo-2).getCell(5).getCellFormula())+" - "+sheet.getRow(rowNo-2).getCell(5).getCellFormula()+"*0.15");
//				C.setCellFormula((sheet.getRow(rowNo-1).getCell(5).getCellFormula())+" - (("+sheet.getRow(rowNo-1).getCell(5).getCellFormula()+")*"+reward.getC_Tax().getRate()+")");
				C.setCellFormula("F"+(rowNo)+"-(F"+rowNo+"*"+reward.getC_Tax().getRate()+")");
				C.setCellStyle(csNumber);
				
				//col7
				C = R.createCell(6);
//				C.setCellFormula(sheet.getRow(rowNo).getCell(5).getCellFormula());
				C.setCellFormula("F"+(rowNo+1));
				C.setCellStyle(csNumber);
				
				tempFormula = "";
			}
			//
			rowNo++;
		}
		//Delete temp styles
		R = sheet.getRow(0);
		CellStyle csEmpty = R.getCell(3).getCellStyle();
		
		C = R.getCell(0);
		C.setCellValue("");
		C.setCellStyle(csEmpty);
		
		C = R.getCell(1);
		C.setCellValue("");
		C.setCellStyle(csEmpty);
		
		C = R.getCell(2);
		C.setCellValue("");
		C.setCellStyle(csEmpty);
		
		C = R.getCell(3);
		C.setCellValue("");
		
		C = R.getCell(4);
		C.setCellValue("");
		C.setCellStyle(csEmpty);	
		
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));	
		return Msg.translate(m_ctx, "Success");
	}
}
